Create DML Left Join in dataset.

DML Left-Join tutorial: From this document you will learn to create the DML dataset which contains left join, here the left join connects the multiple tables to make the DML dataset.

Follow steps to create DML Left-join dataset in OPNBI:-

1. Go to Dataset section, Click on Create DML from bottom menu bar. As shown in figure below:

2. The Create Data Module dialog box looks as shown in figure below:

3. Enter Following detail in DML Module dialog box: Name: DML Left Join Source: ClassicModels Expend tables from left side table view, As shown in figure below:

4. Drag and Drop the OFFICE table from table box and drag to diagram box at right side, and select all the columns from Select Column dialog box, as shown in figure below:

5. Click on Save button, after drag & drop OFFICE table in diagram box, look like figure below:

6. Drag and drop second table, take CUSTOMERS table and drop it in diagram box.

7. After dropping the CUSTOMERS table, select all the columns form the Select Columns dialog box.

8. Click on save button, offices and customers table from diagram box will looks like figure below:

9. Take COUNTRY Column form the office table and drag and connect with customers table in diagram box, as shown in figure below:

10. Drag and drop over the other table as shown in figure.

11. The connected tables will look like figure below:

12. Now to change the DML configuration from inner join to left join, click on the table connection line, as shown figure below:

13. The DML configuration control box will open, as shown in figure below:

14. Select Left Join radio button, click on save button to save the changes.

15. The Connection between two tables will looks as shown in figure below.

16. Click on Save icon from the bottom of the Data Module dialog box. then the Created Successfully message will pop up on the screen.

17. Create Dashboard from the Visualization section. Example name: DML Left Join, as shown in figure below:

18. After loading the Dashboard, Find Analysis Icon from the left vertical menu bar, click on the icon to load analysis widget in dashboard.

19. The Analysis widget will get load in dashboard automatically after clicking on the analysis icon.

20. Select Dataset as DML Left join, as shown in figure below:

21. Now enable the Edit Analysis by clicking the checkbox of Edit Analysis.

22. Make the Analysis widget’s size as you required.

23. Click on Field icon from widget, as shown in figure below:

24. The Analysis Selection dialog box will open, in the dialog box from the Datasets (dml) dropdown select the Dataset as DML left join. As shown in figure below:

25. The two table will get load the left side box of DML view, Now expends the CUSTOMERS table.

26. From the CUSTOMERS table, Drag and drop the COUNTRY column in Row Field, as shown in figure below:

27. The Value filed will get filled with the Count of COUNTRY automatically.

28. Click on Preview button, as you click on preview the Analysis widget will start to show the values from the column fields you selected, Click on Save and Exit button.

29. Click on the Save button to save the dashboard, click on the Preview Icon to see the dashboard at preview side.

30. The Analysis widget with using of DML Left Join looks as shown in figure below:

More features of OPNBI as follows:

DML Inner Join

DML Left Join

DML Right Join

DML Language Alias

DML using Parameter

DML with multiple join condition